package cn.portx.view;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.bstek.bdf2.core.context.ContextHolder;
import com.bstek.bdf3.dorado.jpa.JpaUtil;
import com.bstek.bdf3.saas.Constants;
import com.bstek.bdf3.saas.SaasUtils;
import com.bstek.bdf3.saas.command.Command;
import com.bstek.dorado.annotation.DataProvider;
import com.bstek.dorado.annotation.Expose;

import cn.portx.entity.ExpenseItem;
import cn.portx.entity.ShippingCompany;
import cn.portx.entity.Vessel;
import cn.portx.frame.entity.Zone;

@Component
public class ZonePR {

	@DataProvider
	@Transactional
	public synchronized List<Zone> loadZone(String parentId) throws Exception {
		List<Zone> list = JpaUtil.linq(Zone.class).addIf(parentId).equal("parentId", parentId).endIf().addIfNot(parentId).isNull("parentId").endIf().asc("code").list();
		if (StringUtils.isEmpty(parentId)) {
			if (list.isEmpty()) {
				SaasUtils.doNonQuery(() -> {
					try {
						list.clear();
						list.addAll(this.initZone(2, null));
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
				});
			}
		} else {
			if (list.isEmpty()) {
				Zone parent = JpaUtil.linq(Zone.class).idEqual(parentId).findOne();
				SaasUtils.doNonQuery(() -> {
					try {
						list.clear();
						list.addAll(this.initZone(parent.getLevel() + 1, parent));
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
				});
			}
		}
		return list;
	}

	private List<Zone> initZone(int level, Zone parent) throws Exception {
		List<Zone> list = new ArrayList<Zone>();
		if (level > 5) {
			return list;
		}
		org.springframework.core.io.Resource fileRource = new ClassPathResource("GeoDivision.xlsx");
		Workbook deptWb = WorkbookFactory.create(fileRource.getInputStream());
		if (level == 2) {
			//省
			Sheet sheet3 = deptWb.getSheetAt(2);
			for (int m = sheet3.getFirstRowNum() + 1; m < sheet3.getLastRowNum() + 1; m++) {
				Row row = sheet3.getRow(m);
				if (StringUtils.equals("中国", StringUtils.trim(row.getCell(2).getStringCellValue()))) {
					Zone zone3 = new Zone();
					zone3.setCode(StringUtils.trim(row.getCell(0).getStringCellValue()));
					zone3.setName(StringUtils.trim(row.getCell(1).getStringCellValue()));
					zone3.setLevel(2);
					zone3.setChildren(new ArrayList<Zone>());
					list.add(zone3);
				}
			}
			JpaUtil.persist(list);
		} else if (level == 3) {
			//市
			Sheet sheet4 = deptWb.getSheetAt(3);
			for (int n = sheet4.getFirstRowNum() + 1; n < sheet4.getLastRowNum() + 1; n++) {
				Row row = sheet4.getRow(n);
				if (StringUtils.equals(parent.getName(), StringUtils.trim(row.getCell(2).getStringCellValue()))) {
					Zone zone4 = new Zone();
					zone4.setParentId(parent.getId());
					zone4.setCode(StringUtils.trim(row.getCell(0).getStringCellValue()));
					zone4.setName(StringUtils.trim(row.getCell(1).getStringCellValue()));
					zone4.setLevel(3);
					zone4.setChildren(new ArrayList<Zone>());
					list.add(zone4);
				}
			}
			JpaUtil.persist(list);
		} else if (level == 4) {
			//区县
			Sheet sheet5 = deptWb.getSheetAt(4);
			for (int x = sheet5.getFirstRowNum() + 2; x < sheet5.getLastRowNum() + 1; x++) {
				Row row = sheet5.getRow(x);
				if (StringUtils.equals(parent.getName(), StringUtils.trim(row.getCell(2).getStringCellValue()))) {
					Zone zone5 = new Zone();
					zone5.setParentId(parent.getId());
					zone5.setCode(StringUtils.trim(row.getCell(0).getStringCellValue()));
					zone5.setName(StringUtils.trim(row.getCell(1).getStringCellValue()));
					zone5.setLevel(4);
					zone5.setChildren(new ArrayList<Zone>());
					list.add(zone5);
				}
			}
			JpaUtil.persist(list);
		} else if (level == 5) {
			//街道
			Sheet sheet6 = deptWb.getSheetAt(5);
			for (int y = sheet6.getFirstRowNum() + 2; y < sheet6.getLastRowNum() + 1; y++) {
				Row row = sheet6.getRow(y);
				if (StringUtils.equals(parent.getName(), StringUtils.trim(row.getCell(2).getStringCellValue()))) {
					Zone zone6 = new Zone();
					zone6.setParentId(parent.getId());
					zone6.setCode(StringUtils.trim(row.getCell(0).getStringCellValue()));
					zone6.setName(StringUtils.trim(row.getCell(1).getStringCellValue()));
					zone6.setLevel(5);
					zone6.setChildren(new ArrayList<Zone>());
					list.add(zone6);
				}
			}
			JpaUtil.persist(list);
		}
//		Zone zone2 = new Zone();
//		zone2.setCode("CN");
//		zone2.setName("中国");
//		zone2.setLevel(1);
//		zone2.setChildren(new ArrayList<Zone>());
//		// 省
//		Sheet sheet3 = deptWb.getSheetAt(2);
//		for (int m = sheet3.getFirstRowNum() + 1; m < sheet3.getLastRowNum() + 1; m++) {
//			Row row = sheet3.getRow(m);
//			if (StringUtils.equals(zone2.getName(), StringUtils.trim(row.getCell(2).getStringCellValue()))) {
//				Zone zone3 = new Zone();
//				zone3.setParentId(zone2.getId());
//				zone2.getChildren().add(zone3);
//				zone3.setCode(StringUtils.trim(row.getCell(0).getStringCellValue()));
//				zone3.setName(StringUtils.trim(row.getCell(1).getStringCellValue()));
//				zone3.setLevel(2);
//				zone3.setChildren(new ArrayList<Zone>());
//				JpaUtil.persist(zone3);
//				// 市
//				Sheet sheet4 = deptWb.getSheetAt(3);
//				for (int n = sheet4.getFirstRowNum() + 1; n < sheet4.getLastRowNum() + 1; n++) {
//					row = sheet4.getRow(n);
//					if (StringUtils.equals(zone3.getName(), StringUtils.trim(row.getCell(2).getStringCellValue()))) {
//						Zone zone4 = new Zone();
//						zone4.setParentId(zone3.getId());
//						zone3.getChildren().add(zone4);
//						zone4.setCode(StringUtils.trim(row.getCell(0).getStringCellValue()));
//						zone4.setName(StringUtils.trim(row.getCell(1).getStringCellValue()));
//						zone4.setLevel(3);
//						zone4.setChildren(new ArrayList<Zone>());
//						JpaUtil.persist(zone4);
//						// 区县
//						Sheet sheet5 = deptWb.getSheetAt(4);
//						for (int x = sheet5.getFirstRowNum() + 2; x < sheet5.getLastRowNum() + 1; x++) {
//							row = sheet5.getRow(x);
//							if (StringUtils.equals(zone4.getName(), StringUtils.trim(row.getCell(2).getStringCellValue()))) {
//								Zone zone5 = new Zone();
//								zone5.setParentId(zone4.getId());
//								zone4.getChildren().add(zone5);
//								zone5.setCode(StringUtils.trim(row.getCell(0).getStringCellValue()));
//								zone5.setName(StringUtils.trim(row.getCell(1).getStringCellValue()));
//								zone5.setLevel(4);
//								zone5.setChildren(new ArrayList<Zone>());
//								JpaUtil.persist(zone5);
//								// 街道
//								Sheet sheet6 = deptWb.getSheetAt(5);
//								for (int y = sheet6.getFirstRowNum() + 2; y < sheet6.getLastRowNum() + 1; y++) {
//									row = sheet6.getRow(y);
//									if (StringUtils.equals(zone5.getName(), StringUtils.trim(row.getCell(2).getStringCellValue()))) {
//										Zone zone6 = new Zone();
//										zone6.setParentId(zone5.getId());
//										zone5.getChildren().add(zone6);
//										zone6.setCode(StringUtils.trim(row.getCell(0).getStringCellValue()));
//										zone6.setName(StringUtils.trim(row.getCell(1).getStringCellValue()));
//										zone6.setLevel(5);
//										zone6.setChildren(new ArrayList<Zone>());
//										JpaUtil.persist(zone6);
//									}
//								}
//							}
//						}
//					}
//				}
//			}
//		}
		return list;
	}

	private void parseShippingCompany() throws Exception {
		org.springframework.core.io.Resource fileRource = new ClassPathResource("货代船名、船公司.xlsx");
		Workbook wb = WorkbookFactory.create(fileRource.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Map<String, ShippingCompany> map = new HashMap<String, ShippingCompany>();
		for (int i = sheet.getFirstRowNum() + 1; i < sheet.getLastRowNum() + 1; i++) {
			Row row = sheet.getRow(i);
			String code = row.getCell(0).getStringCellValue();
			ShippingCompany sc = null;
			if (!map.containsKey(code)) {
				sc = new ShippingCompany();
				sc.setCode(code);
				sc.setName(row.getCell(1).getStringCellValue());
				JpaUtil.persist(sc);
				map.put(code, sc);
			} else {
				sc = map.get(code);
			}
			Vessel v = new Vessel();
			v.setName(row.getCell(2).getStringCellValue());
			v.setShippingCompanyId(sc.getId());
			v.setType("0");
			JpaUtil.persist(v);
		}

		fileRource = new ClassPathResource("费用信息.xlsx");
		wb = WorkbookFactory.create(fileRource.getInputStream());
		sheet = wb.getSheetAt(0);
		for (int i = sheet.getFirstRowNum() + 1; i < sheet.getLastRowNum() + 1; i++) {
			Row row = sheet.getRow(i);
			ExpenseItem ei = new ExpenseItem();
			ei.setName(row.getCell(0).getStringCellValue());
			ei.setCode(row.getCell(1).getStringCellValue());
			Double type = row.getCell(2).getNumericCellValue();
			ei.setType(type == null ? null : String.valueOf(type.intValue()));
			Double icu = row.getCell(3).getNumericCellValue();
			ei.setInCommonUse(icu == null ? null : icu.intValue());
			Double dw = row.getCell(4) == null ? null : row.getCell(4).getNumericCellValue();
			ei.setDw(dw == null ? null : String.valueOf(dw.intValue()));
			JpaUtil.persist(ei);
		}
	}

	@Expose
	public void initData(List<String> orgIds) throws IllegalAccessException {
		if(!StringUtils.equals(Constants.MASTER, ContextHolder.getLoginOrgId())){
			throw new IllegalAccessException();
		}
		for (String orgId : orgIds) {
			SaasUtils.doNonQuery(orgId, new Command() {

				@Override
				public void execute() {
					try {
						parse();
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
				}

			});
		}
	}

	private void parse() throws Exception {
		Map<String, ShippingCompany> map1 = new HashMap<String, ShippingCompany>();
		Map<String, List<Vessel>> map2 = new HashMap<String, List<Vessel>>();

		org.springframework.core.io.Resource fileRource = new ClassPathResource("货代船名、船公司.xlsx");
		Workbook wb = WorkbookFactory.create(fileRource.getInputStream());
		Sheet sheet = wb.getSheetAt(0);

		for (int i = sheet.getFirstRowNum() + 1; i < sheet.getLastRowNum() + 1; i++) {
			Row row = sheet.getRow(i);
			String code = row.getCell(0).getStringCellValue();
			ShippingCompany sc = null;
			if (!map1.containsKey(code)) {
				sc = new ShippingCompany();
				sc.setId(UUID.randomUUID().toString());
				sc.setCode(code);
				sc.setName(row.getCell(1).getStringCellValue());
				map1.put(code, sc);
			} else {
				sc = map1.get(code);
			}
			List<Vessel> vList = null;
			if (!map2.containsKey(code)) {
				vList = new ArrayList<Vessel>();
				map2.put(code, vList);
			} else {
				vList = map2.get(code);
			}
			Vessel v = new Vessel();
			v.setName(row.getCell(2).getStringCellValue());
			v.setShippingCompanyId(sc.getId());
			v.setType("0");

			vList.add(v);
		}
		List<ShippingCompany> list = JpaUtil.linq(ShippingCompany.class).in("code", map1.keySet()).list();
		if (list != null && !list.isEmpty()) {
			for (ShippingCompany sc : list) {

				map1.remove(sc.getCode());

				List<Vessel> excelData = map2.get(sc.getCode());
				List<Vessel> dbData = JpaUtil.linq(Vessel.class).equal("shippingCompanyId", sc.getId()).list();

				for (Iterator<Vessel> iter = excelData.iterator(); iter.hasNext();) {
					Vessel v = iter.next();
					if (this.containsVessel(dbData, v.getCode())) {
						iter.remove();
					} else {
						v.setShippingCompanyId(sc.getId());
					}
				}
				dbData = null;
			}
		}
		JpaUtil.persist(map1.values());
		for (Iterator<String> iter = map2.keySet().iterator(); iter.hasNext();) {
			String code = iter.next();
			List<Vessel> vList = map2.get(code);
			if (!vList.isEmpty()) {
				JpaUtil.persist(vList);
			}
		}
		map1 = null;
		map2 = null;
		list = null;
//-----------------------------------------------------------------------------------------------------
		fileRource = new ClassPathResource("费用信息.xlsx");
		wb = WorkbookFactory.create(fileRource.getInputStream());
		sheet = wb.getSheetAt(0);
		List<ExpenseItem> eiList = new ArrayList<ExpenseItem>();
		for (int i = sheet.getFirstRowNum() + 1; i < sheet.getLastRowNum() + 1; i++) {
			Row row = sheet.getRow(i);
			ExpenseItem ei = new ExpenseItem();
			ei.setName(row.getCell(0).getStringCellValue());
			ei.setCode(row.getCell(1).getStringCellValue());
			Double type = row.getCell(2) == null ? null : row.getCell(2).getNumericCellValue();
			ei.setType(type == null ? null : String.valueOf(type.intValue()));
			Double icu = row.getCell(3) == null ? null : row.getCell(3).getNumericCellValue();
			ei.setInCommonUse(icu == null ? null : icu.intValue());
			Double dw = row.getCell(4) == null ? null : row.getCell(4).getNumericCellValue();

			ei.setDw(dw == null ? null : (dw == -100 ? null : String.valueOf(dw.intValue())));

			eiList.add(ei);

		}

		List<ExpenseItem> dbEiList = JpaUtil.linq(ExpenseItem.class).list();
		for (ExpenseItem ei : dbEiList) {
			for (Iterator<ExpenseItem> iter = eiList.iterator(); iter.hasNext();) {
				ExpenseItem e = iter.next();
				if (StringUtils.equals(e.getCode(), ei.getCode()) && StringUtils.equals(e.getType(), ei.getType()) && StringUtils.equals(e.getDw(), ei.getDw())) {
					iter.remove();
					break;
				}
			}
		}
		JpaUtil.persist(eiList);

		eiList = null;
		dbEiList = null;
	}

	private boolean containsVessel(List<Vessel> list, String code) {
		for (Vessel sc : list) {
			if (StringUtils.equals(code, sc.getCode())) {
				return true;
			}
		}
		return false;

	}
}
